package com.sxyjhh.dbutil.meta;

import com.google.common.annotations.VisibleForTesting;
import com.google.common.base.Preconditions;
import com.sxyjhh.dbutil.basic.ConstConfig;
import org.apache.commons.lang.StringUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;

/**
 * Description:数据元数据基本操作类
 *
 * @JDK Version:1.8.0_40
 * Version: V1.0
 * Author: wangchun
 * Date: 2016/3/29 10:05
 */
public class DatabaseAnalyzer {

    public static void cleanDatabase(Connection connection, ConstConfig.DBType dbType) throws SQLException {
        String dbName = connection.getCatalog();
        List<TableMetaData> tables = getTables(connection, dbName, dbType);
        for (TableMetaData table : tables) {
            dropForeignKeys(connection, table);
        }
        for (TableMetaData table : tables) {
            deleteAll(connection, table.getName());
        }
        for (TableMetaData table : tables) {
            addForeignKeys(connection, table);
        }
    }

    public static void dropForeignKeys(Connection connection, TableMetaData tableMetaData) throws SQLException {
        for (ForeignKeyMetaData foreignKey : tableMetaData.getForeignKeys()) {
            dropForeignKey(connection, foreignKey);
        }
    }

    @VisibleForTesting
    public static void dropForeignKey(Connection connection, ForeignKeyMetaData foreignKey) throws SQLException {
        PreparedStatement stmt = connection.prepareStatement(String.format("alter table `%s` drop  foreign key `%s` ", foreignKey.getFkTable(), foreignKey.getFkName()));
        stmt.execute();
        stmt.close();
    }

    public static void addForeignKeys(Connection connection, TableMetaData tableMetaData) throws SQLException {
        for (ForeignKeyMetaData foreignKey : tableMetaData.getForeignKeys()) {
            addForeignKey(connection, foreignKey);
        }
    }

    @VisibleForTesting
    public static void addForeignKey(Connection connection, ForeignKeyMetaData foreignKey) throws SQLException {
        String sql = String.format("alter table `%s` add constraint `%s` foreign  key (`%s`) references `%s`(`%s`)", foreignKey.getFkTable(), foreignKey.getFkName(), foreignKey.getFkColumn(), foreignKey.getPkTable(), foreignKey.getPkColumn());
        PreparedStatement stmt = connection.prepareStatement(sql);
        stmt.execute();
        stmt.close();
    }

    public static void deleteAll(Connection connection, String tableName) throws SQLException {
        PreparedStatement stmt = connection.prepareStatement(String.format("delete from %s", tableName));
        stmt.execute();
        stmt.close();
    }

    public static List<TableMetaData> getTables(Connection connection, String dbName, ConstConfig.DBType dbType) throws SQLException {
        Preconditions.checkArgument(!connection.isClosed(), "connection is closed");
        DatabaseMetaData dbMeta = connection.getMetaData();

        List<TableMetaData> tables = new LinkedList<TableMetaData>();

        ResultSet tablesResultSet = dbMeta.getTables(dbName, null, null, new String[]{"TABLE"});
        while (tablesResultSet.next()) {
            String tableName = tablesResultSet.getString("TABLE_NAME");
            tables.add(new TableMetaData(tableName, getForeignKeys(dbMeta, dbName, tableName), getColumns(dbMeta, dbName, tableName, dbType), getPrimaryKeys(dbMeta, dbName, tableName)));
        }
        return tables;
    }

    public static List<TableMetaData> getTablesSimple(Connection connection, String dbName) throws SQLException {
        Preconditions.checkArgument(!connection.isClosed(), "connection is closed");
        DatabaseMetaData dbMeta = connection.getMetaData();
        List<TableMetaData> tables = new LinkedList<TableMetaData>();

        String driverName = connection.getMetaData().getDriverName();
        //需要判断数据库类型，组装不同的查询条件
        if (StringUtils.startsWithIgnoreCase(driverName, "Oracle")) {
            dbName = dbMeta.getUserName();
        } else if (StringUtils.startsWithIgnoreCase(driverName, "Microsoft")) {
            dbName = null;
        }

        ResultSet tablesResultSet = dbMeta.getTables(connection.getCatalog(), dbName, null, new String[]{"TABLE"});
        while (tablesResultSet.next()) {
            String tableName = tablesResultSet.getString("TABLE_NAME");
            String remark = tablesResultSet.getString("REMARKS");
            tables.add(new TableMetaData(tableName, remark));
        }
        return tables;
    }

    public static List<TableMetaData> getTablesSimple(Connection connection, String dbName, String otherUser) throws SQLException {
        Preconditions.checkArgument(!connection.isClosed(), "connection is closed");
        DatabaseMetaData dbMeta = connection.getMetaData();
        List<TableMetaData> tables = new LinkedList<TableMetaData>();

        String driverName = connection.getMetaData().getDriverName();
        //需要判断数据库类型，组装不同的查询条件
        if (StringUtils.startsWithIgnoreCase(driverName, "Oracle")) {
            dbName = StringUtils.isNotEmpty(otherUser) ? otherUser : dbMeta.getUserName();
        } else if (StringUtils.startsWithIgnoreCase(driverName, "Microsoft")) {
            dbName = null;
        }

        ResultSet tablesResultSet = dbMeta.getTables(connection.getCatalog(), dbName, null, new String[]{"TABLE"});
        while (tablesResultSet.next()) {
            String tableName = tablesResultSet.getString("TABLE_NAME");
            String remark = tablesResultSet.getString("REMARKS");
            tables.add(new TableMetaData(tableName, remark));
        }
        return tables;
    }

    public static TableMetaData getTablesSimpleByTName(Connection connection, String dbName, String tableName) throws SQLException {
        Preconditions.checkArgument(!connection.isClosed(), "connection is closed");
        DatabaseMetaData dbMeta = connection.getMetaData();
        List<TableMetaData> tables = new LinkedList<TableMetaData>();

        String driverName = connection.getMetaData().getDriverName();
        //需要判断数据库类型，组装不同的查询条件
        if (StringUtils.startsWithIgnoreCase(driverName, "Oracle")) {
            dbName = dbMeta.getUserName();
        } else if (StringUtils.startsWithIgnoreCase(driverName, "Microsoft")) {
            dbName = null;
        }

        ResultSet tablesResultSet = dbMeta.getTables(connection.getCatalog(), dbName, tableName, new String[]{"TABLE"});
        while (tablesResultSet.next()) {
            String tableNameTemp = tablesResultSet.getString("TABLE_NAME");
            String remark = tablesResultSet.getString("REMARKS");
            tables.add(new TableMetaData(tableNameTemp, remark));
        }
        return tables.size() > 0 ? tables.get(0) : null;
    }

    public static List<ColumnMetaData> getColumns(DatabaseMetaData dbMeta, String dbName, String tableName, ConstConfig.DBType dbType) throws SQLException {
        List<ColumnMetaData> columns = new LinkedList<ColumnMetaData>();
        if (dbType == ConstConfig.DBType.ORACLE) {
            dbName = dbMeta.getUserName();
        } else if (dbType != ConstConfig.DBType.DAMENG) {
            dbName = "%";
        }
        String userName = dbMeta.getUserName();
        ResultSet columnResultSet = dbMeta.getColumns(null, dbName, tableName, "%");
        while (columnResultSet.next()) {
            String columnName = columnResultSet.getString("COLUMN_NAME");
            String columnType = columnResultSet.getString("TYPE_NAME");
            String remark = columnResultSet.getString("REMARKS");
            Integer length = columnResultSet.getInt("COLUMN_SIZE");
            Integer digitsLength = columnResultSet.getInt("DECIMAL_DIGITS");
            columns.add(new ColumnMetaData(columnName, columnType, remark, length, digitsLength));
        }
        return columns;
    }

    public static List<ColumnMetaData> getColumns(DatabaseMetaData dbMeta, String dbName, String tableName, ConstConfig.DBType dbType, String otherUser) throws SQLException {
        List<ColumnMetaData> columns = new LinkedList<ColumnMetaData>();
        if (dbType == ConstConfig.DBType.ORACLE) {
            dbName = StringUtils.isNotEmpty(otherUser) ? otherUser : dbMeta.getUserName();
        } else if (dbType != ConstConfig.DBType.DAMENG) {
            dbName = "%";
        }
        String userName = dbMeta.getUserName();
        ResultSet columnResultSet = dbMeta.getColumns(null, dbName, tableName.toUpperCase(), "%");
        while (columnResultSet.next()) {
            String columnName = columnResultSet.getString("COLUMN_NAME");
            String columnType = columnResultSet.getString("TYPE_NAME");
            String remark = columnResultSet.getString("REMARKS");
            Integer length = columnResultSet.getInt("COLUMN_SIZE");
            Integer digitsLength = columnResultSet.getInt("DECIMAL_DIGITS");
            columns.add(new ColumnMetaData(columnName, columnType, remark, length, digitsLength));
        }
        return columns;
    }

    public static List<PrimaryKeyMetaData> getPrimaryKeys(DatabaseMetaData dbMeta, String dbName, String tableName) throws SQLException {
        List<PrimaryKeyMetaData> pkList = new ArrayList<>();
        ResultSet primaryKeySet = dbMeta.getPrimaryKeys(null, null, tableName);
        while (primaryKeySet.next()) {
            String columnName = primaryKeySet.getString("COLUMN_NAME");
            //Oracle结果中无字段TYPE_NAME
            pkList.add(new PrimaryKeyMetaData(columnName, "TYPE_NAME"));
        }
        return pkList;
    }

    public static List<ForeignKeyMetaData> getForeignKeys(DatabaseMetaData dbMeta, String dbName, String tableName) throws SQLException {
        List<ForeignKeyMetaData> foreignKeys = new LinkedList<>();
        ResultSet foreignKeysResultSet = dbMeta.getImportedKeys(dbName, dbName.toUpperCase(), tableName);
        while (foreignKeysResultSet.next()) {
            String fkTableName = foreignKeysResultSet.getString("FKTABLE_NAME");
            String fkColumnName = foreignKeysResultSet.getString("FKCOLUMN_NAME");
            String fkName = foreignKeysResultSet.getString("FK_NAME");
            String pkTableName = foreignKeysResultSet.getString("PKTABLE_NAME");
            String pkColumnName = foreignKeysResultSet.getString("PKCOLUMN_NAME");
            foreignKeys.add(new ForeignKeyMetaData(pkTableName, pkColumnName, fkTableName, fkColumnName, fkName));
        }
        return foreignKeys;
    }

    public static List<String> getCatalogs(DatabaseMetaData dbMeta) throws SQLException {
        List<String> catalogs = new LinkedList<>();
        ResultSet catalogResultSet = dbMeta.getCatalogs();
        while (catalogResultSet.next()) {
            catalogs.add(catalogResultSet.getString("TABLE_CAT"));
        }
        return catalogs;
    }
}
